package com.dhcc.DRGsHosp.application.impl;

import java.util.List;
import java.util.Map;
import javax.inject.Named;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import com.dhcc.DRGsHosp.application.DisStructureAnalysisApplication;
import com.dhcc.DRGsHosp.core.domain.DrgsAnalysis;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

/**  
* @ClassName: DisStructureAnalysisApplicationImpl  
* @Description: 病组结构分析
* @author Administrator
* @date 2018年8月31日  
* @author shaozhiyuan  
*/
@Named
public class DisStructureAnalysisApplicationImpl implements DisStructureAnalysisApplication {

	@Autowired
    private JdbcTemplateObj jdbcTemplateObj;
	
	/**  
	 * @Description:病组结构分析(表格数据获取) 
	 * @author shaozhiyuan 
	 * @date 2018年8月31日  
	 * @version V3.1.1 
	 */  
	@Override
	public List<Map<String, Object>> getStructureData(IndexCal indexCal) {
		String dateFrom= DrgsIndex.formateDateFrom(indexCal.getDateFrom()); //本期起始时间
		String dateTo=DrgsIndex.formateDateTo(indexCal.getDateTo());	//本期结束时间
		String monthFrom=indexCal.getDateFrom().substring(5);	//起始月份
		String monthTo=indexCal.getDateTo().substring(5);	//起始月份
		String hospCode = indexCal.getHospCode(); //院区代码
		String pardepgroupCode = indexCal.getPardepgroupCode(); //科室组代码
		String depCode = indexCal.getPardepCode();  //科室代码
		String stdId = indexCal.getStdId(); //获取标杆定位id
		String groupScheme = indexCal.getGroupType(); //获取分组方案
		String pType = indexCal.getpType();  //是否医保数据 1是医保
		
		String thisYearFrom=indexCal.getDateFrom().substring(0, 4);
		String thisYearTo=indexCal.getDateTo().substring(0, 4);
		//本期日期段
		String dateStr=""; //本期日期段
		String lastDateStr=""; //同期日期段
		if(indexCal.getDateFrom()!=null&&indexCal.getDateFrom()!=""
				&&indexCal.getDateTo()!=null&&indexCal.getDateTo()!=""){
			if(monthFrom==monthTo){
				dateStr=thisYearFrom+"."+monthFrom;
				lastDateStr=(Integer.parseInt(thisYearFrom)-1)+"."+monthFrom;
			}else{
				dateStr=thisYearFrom+"."+monthFrom
						+"~"+thisYearTo+"."+monthTo;
				lastDateStr=(Integer.parseInt(thisYearFrom)-1)+"."+monthFrom+"~"
						+(Integer.parseInt(thisYearTo)-1)+"."+monthTo;
			}
		}
		
		StringBuilder sql = new StringBuilder();
		
		//院区条件字符串
		String hospCondition = "";
		if (StringUtils.hasLength(hospCode)&&!"null".equals(hospCode)) {
			hospCondition = " and instr('"+hospCode+"', ba.p00010001)>0 ";
		} 
		
		//科室组条件字符串
		String depGroupCondition = "";
		if (StringUtils.hasLength(pardepgroupCode)&&!"null".equals(pardepgroupCode)&&!"hosp".equals(pardepgroupCode)) {
			depGroupCondition = "and dep.pardepgroup_code = '"+pardepgroupCode+"'";
		} 
		
		//科室条件字符串
		String depCondition = "";
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1){//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				depCondition = "and dep.dep_code in ("+depCode+")";
			}else{//一个科室
				depCondition = "and dep.dep_code = '"+depCode+"'";
			}
		}
		
		String tableCondition = ""; //系统相关表关联拼接
		tableCondition =" from drgs.mr_baseinfo ba  \n ";
		tableCondition+=" left join drgs.mr_ipinfo ip on ba.pmainindex = ip.pmainindex \n "; 
		tableCondition+=" left join drgs.t_patientdrg pd on ip.pmainindex = pd.parid  and  pd.ver_region='"+groupScheme+"' \n ";
		tableCondition+=" left join drgs.t_annualstd ad on ad.as_drg_code=pd.pardrg_code and ad.as_id='"+stdId+"' \n ";
		tableCondition+=" left join DRGs.t_department dep on  ip.P00300001=dep.dep_code \n ";
		tableCondition+=" LEFT JOIN DRGS.T_MEDINS MEDINS ON MEDINS.PARID = ip.PMAININDEX   \n ";
		if("1".equals(pType)){//医保条件
			tableCondition+="  and MEDINS.parid is not null  \n ";
		}
		tableCondition+=" where pd.pardrg_code is not null "+depCondition+depGroupCondition+hospCondition+" \n ";
		
		String depDrgCondition = ""; //理论覆盖病组的相关表关联拼接(不带select条件)
		depDrgCondition =" from DRGs.t_departmentdrg depdrg  \n ";
		depDrgCondition+=" left join DRGs.t_department dep on  dep.dep_code=depdrg.pardep_code \n ";
		depDrgCondition+=" where 1=1 \n ";
		depDrgCondition+= depGroupCondition+depCondition; //科室条件
		depDrgCondition+=" and depdrg.version = ( \n ";
		depDrgCondition+=" SELECT a.ver_code  FROM ( \n ";
		depDrgCondition+=" select  pd.ver_code,count(1) cnt from drgs.t_patientdrg pd \n ";
		depDrgCondition+=" where pd.ver_code IS NOT NULL and pd.ver_region='"+groupScheme+"' \n ";
		depDrgCondition+=" group BY pd.ver_code ) a  WHERE Rownum=1 ) \n ";
		
		sql.setLength(0);
		//本期值
		sql.append(" select * from ( \n ");
		sql.append("  SELECT * FROM  ( SELECT 1 ordernum, A.MITYPE,A.CNT,A.CMI , A.DRGCNT , B.INDRGCNT, A.DRGCNT-B.INDRGCNT OUTDRGCNT \n ");
		sql.append(" ,C.pardrg-B.INDRGCNT NOTINDRGCNT ,round(decode(C.pardrg,0,0, B.INDRGCNT/C.pardrg)*100,2) RATIO FROM \n ");
		sql.append(" ( select '本期（"+dateStr+"）覆盖病组' MITYPE , count(pd.parid) CNT \n ");
		sql.append(" ,ROUND(decode(COUNT(pd.parid),0,0,SUM(ad.as_weight)/COUNT(pd.PARID)),2) CMI \n ");
		sql.append(" ,count(distinct pd.pardrg_code) DRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' ) A \n ");
		sql.append(" ,( select count(distinct pd.pardrg_code) INDRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' and pd.pardrg_code in ( \n ");
		sql.append(" select depdrg.pardrg_code \n ");
		sql.append( depDrgCondition );
		sql.append(" ) ) B,	( select count( distinct depdrg.pardrg_code) pardrg \n ");
		sql.append( depDrgCondition );
		sql.append("  )C )  UNION \n ");
		//同期
		sql.append(" SELECT * FROM ( SELECT 2 ordernum, A.MITYPE,A.CNT,A.CMI , A.DRGCNT , B.INDRGCNT, A.DRGCNT-B.INDRGCNT OUTDRGCNT \n ");
		sql.append(" ,C.pardrg-B.INDRGCNT NOTINDRGCNT ,round( decode(C.pardrg,0,0, B.INDRGCNT/C.pardrg)*100,2) RATIO FROM \n ");
		sql.append(" ( select '同期（"+lastDateStr+"）覆盖病组' MITYPE , count(pd.parid) CNT \n ");
		sql.append(" ,ROUND(decode(COUNT(pd.parid),0,0,SUM(ad.as_weight)/COUNT(pd.PARID)),2) CMI \n ");
		sql.append(" ,count(distinct pd.pardrg_code) DRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000,'yyyy-mm') between  \n ");
		sql.append(" '"+(Integer.parseInt(thisYearFrom)-1)+"-"+monthFrom+"' and '"+(Integer.parseInt(thisYearTo)-1)+"-"+monthTo+"' ) A  \n ");
		sql.append(" ,( select count(distinct pd.pardrg_code) INDRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000,'yyyy-mm') between \n ");
		sql.append(" '"+(Integer.parseInt(thisYearFrom)-1)+"-"+monthFrom+"' and '"+(Integer.parseInt(thisYearTo)-1)+"-"+monthTo+"' \n ");
		sql.append(" and pd.pardrg_code in ( \n ");
		sql.append(" select depdrg.pardrg_code \n ");
		sql.append( depDrgCondition );
		sql.append(" ) ) B,	( select count( distinct depdrg.pardrg_code) pardrg \n ");
		sql.append( depDrgCondition );
		sql.append("  )C )  \n ");
		//仅本期覆盖病组
		sql.append(" UNION \n ");
		sql.append("  SELECT * FROM  ( SELECT 3 ordernum, A.MITYPE,A.CNT,A.CMI , A.DRGCNT , B.INDRGCNT, A.DRGCNT-B.INDRGCNT OUTDRGCNT \n ");
		sql.append(" ,C.pardrg-B.INDRGCNT NOTINDRGCNT ,round( decode(C.pardrg,0,0, B.INDRGCNT/C.pardrg)*100,2) RATIO FROM \n ");
		sql.append(" ( select '仅本期（"+dateStr+"）覆盖病组' MITYPE , count(pd.parid) CNT \n ");
		sql.append(" ,ROUND(decode(COUNT(pd.parid),0,0,SUM(ad.as_weight)/COUNT(pd.PARID)),2) CMI \n ");
		sql.append(" ,count(distinct pd.pardrg_code) DRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and pd.pardrg_code not in (select distinct pd.pardrg_code  \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000,'yyyy-mm') between \n ");
		sql.append(" '"+(Integer.parseInt(thisYearFrom)-1)+"-"+monthFrom+"' and '"+(Integer.parseInt(thisYearTo)-1)+"-"+monthTo+"' \n ");
		sql.append(" ) and to_char(ip.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' ) A \n ");
		sql.append(" ,( select count(distinct pd.pardrg_code) INDRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and pd.pardrg_code not in (select distinct pd.pardrg_code  \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000,'yyyy-mm') between \n ");
		sql.append(" '"+(Integer.parseInt(thisYearFrom)-1)+"-"+monthFrom+"' and '"+(Integer.parseInt(thisYearTo)-1)+"-"+monthTo+"' \n ");
		sql.append(" ) and to_char(ip.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' and pd.pardrg_code in ( \n ");
		sql.append(" select depdrg.pardrg_code \n ");
		sql.append( depDrgCondition );
		sql.append(" ) ) B,	( select count( distinct depdrg.pardrg_code) pardrg \n ");
		sql.append( depDrgCondition );
		sql.append("  )C )   \n ");
		//仅同期覆盖病组
		sql.append(" UNION SELECT * FROM ( SELECT 4 ordernum, A.MITYPE,A.CNT,A.CMI , A.DRGCNT , B.INDRGCNT, A.DRGCNT-B.INDRGCNT OUTDRGCNT \n ");
		sql.append(" ,C.pardrg-B.INDRGCNT NOTINDRGCNT ,round( decode(C.pardrg,0,0, B.INDRGCNT/C.pardrg)*100,2) RATIO FROM \n ");
		sql.append(" ( select '仅同期（"+lastDateStr+"）覆盖病组' MITYPE , count(pd.parid) CNT \n ");
		sql.append(" ,ROUND(decode(COUNT(pd.parid),0,0,SUM(ad.as_weight)/COUNT(pd.PARID)),2) CMI \n ");
		sql.append(" ,count(distinct pd.pardrg_code) DRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and pd.pardrg_code not in (select distinct pd.pardrg_code  \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000, 'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n ");
		sql.append(" ) and to_char(ip.P00290000,'yyyy-mm') between  \n ");
		sql.append(" '"+(Integer.parseInt(thisYearFrom)-1)+"-"+monthFrom+"' and '"+(Integer.parseInt(thisYearTo)-1)+"-"+monthTo+"' ) A  \n ");
		sql.append(" ,( select count(distinct pd.pardrg_code) INDRGCNT \n ");
		sql.append( tableCondition );
		sql.append(" and pd.pardrg_code not in (select distinct pd.pardrg_code  \n ");
		sql.append( tableCondition );
		sql.append(" and to_char(ip.P00290000, 'yyyy-mm-dd')   between '"+dateFrom+"' and '"+dateTo+"' \n ");
		sql.append(" ) and to_char(ip.P00290000,'yyyy-mm') between \n ");
		sql.append(" '"+(Integer.parseInt(thisYearFrom)-1)+"-"+monthFrom+"' and '"+(Integer.parseInt(thisYearTo)-1)+"-"+monthTo+"' \n ");
		sql.append(" and pd.pardrg_code in ( \n ");
		sql.append(" select depdrg.pardrg_code \n ");
		sql.append( depDrgCondition );
		sql.append(" ) ) B,	( select count( distinct depdrg.pardrg_code) pardrg \n ");
		sql.append( depDrgCondition );
		sql.append("  )C )  ) order by ordernum asc \n ");
		
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}

	/**  
	 * @Description:新版病组结构DRGs病组详情
	 * @author shaozhiyuan 
	 * @date 2018年9月5日  
	 * @version V3.1.1
	 */  
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'getDrgsStrucDetail' + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode"
			+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo + #drgsAnalysis.queryType + #drgsAnalysis.monitorType ")
	public List<Map<String, Object>> getDrgsStrucDetail(DrgsAnalysis drgsAnalysis) {
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
    	String dateFrom= drgsAnalysis.getDateFrom();
		String dateTo=drgsAnalysis.getDateTo();
		String queryType = drgsAnalysis.getQueryType();
		String stdId = drgsAnalysis.getStdId();//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
		int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年
    	String monthFrom=dateFrom.substring(5);	//起始月份
		String monthTo=dateTo.substring(5);	//起始月份
    	String condition = "";//条件字符串
    	//monitorType是 类型比较  all:DRGs组数   in:理论内DRG组数 out:理论外DRG组数 notin:理论内未覆盖DRGs组数
    	String monitorType=drgsAnalysis.getMonitorType(); 
    	
    	//当查询条件 有科室（并且不为全部）并且无病区的情况下，查询数据添加一列 “是否理论应治疗”
    	boolean overage = false;
    	String[] stdIds = stdId.split("_");
    	String conditionOver = "";//是否在理论治疗内条件字符串
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition = "and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition = "and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition = "and dep.dep_code = '"+depCode+"'";
				overage = true;
				conditionOver = "   left join \n "
						+"( select d.pardrg_code from DRGs.t_departmentdrg d \n "
						+" where  d.version = '"+stdIds[0]+"' and d.pardep_code = '"+depCode+"') d \n "
						+" on t.drgCode= d.pardrg_code \n ";
			}
		}
		
		//DRG覆盖内外病组
		String monitorTypeCondition="";
		if(StringUtils.hasLength(monitorType)){
			if("in".equals(monitorType)||"out".equals(monitorType)){
				if("in".equals(monitorType)){
					monitorTypeCondition +=" and b.pardrg_code in (  select depdrg.pardrg_code \n ";
				}else{
					monitorTypeCondition +=" and b.pardrg_code not in (  select depdrg.pardrg_code \n ";
				}
				monitorTypeCondition +=" from DRGs.t_departmentdrg depdrg  \n ";
	    		monitorTypeCondition +=" left join DRGs.t_department dep on  dep.dep_code=depdrg.pardep_code \n ";
	    		monitorTypeCondition +=" where 1=1 \n ";
	    		monitorTypeCondition += condition ; //科室条件
	    		monitorTypeCondition +=" and depdrg.version = ( \n ";
	    		monitorTypeCondition +=" SELECT a.ver_code  FROM ( \n ";
	    		monitorTypeCondition +=" select  pd.ver_code,count(1) cnt from drgs.t_patientdrg pd \n ";
	    		monitorTypeCondition +=" where pd.ver_code IS NOT NULL and pd.ver_region='"+groupScheme+"' \n ";
	    		monitorTypeCondition +=" group BY pd.ver_code ) a  WHERE Rownum=1 ) \n ";
	    		monitorTypeCondition +="   ) \n";
			}
		}
		
		StringBuilder sql  = new StringBuilder();
		switch(queryType)
		{
		case "this":
			sql.setLength(0);
			sql.append("  select rownum lastorder, ");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("  select  a.pardrg_code drgCode,a.drg_desc drgDesc,a.tpcnt tpcnt,a.avgfee avgfee,a.avgday avgday,nvl(b.lpcnt,0) lpcnt,nvl(b.lavgfee,0) lavgfee,nvl(b.lavgday,0) lavgday,c.as_weight weight from  \n ");
			sql.append("  (select a.pardrg_code,a.drg_desc,a.tpcnt,a.avgfee,a.avgday from( \n ");
			sql.append("  select b.pardrg_code,drg.drg_desc,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append( monitorTypeCondition );
			sql.append("  group by b.pardrg_code,drg.drg_desc  \n ");
			sql.append("  order by tpcnt desc ) a) a  \n ");
			sql.append("  left join  \n ");
			sql.append("  (select b.pardrg_code,b.lpcnt,b.lavgfee,b.lavgday from( \n ");
			sql.append("  select b.pardrg_code,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append( monitorTypeCondition );
			sql.append("  group by b.pardrg_code \n ");
			sql.append("  order by lpcnt desc ) b)b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			sql.append("  left join DRGs.t_annualstd c \n ");
			sql.append("  on c.as_drg_code = a.pardrg_code and c.as_id = '"+stdId+"' \n ");
			sql.append("  order by lpcnt desc) t \n ");
			break;
		case "last":
			sql.setLength(0);
			sql.append("  select rownum thisOrder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("  select b.pardrg_code drgCode,b.drg_desc drgDesc,nvl(a.tpcnt,0) tpcnt,nvl(a.avgfee,0) avgfee,nvl(a.avgday,0) avgday,b.lpcnt lpcnt,b.lavgfee lavgfee,b.lavgday lavgday,c.as_weight weight from  \n ");
			sql.append("  (select a.pardrg_code,a.tpcnt,a.avgfee,a.avgday from( \n ");
			sql.append("  select b.pardrg_code,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b  \n ");
			sql.append("  on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'   \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append( monitorTypeCondition );
			sql.append("  group by b.pardrg_code \n ");
			sql.append("  order by tpcnt desc ) a) a  \n ");
			sql.append("  right join  \n ");
			sql.append("  (select b.pardrg_code,b.drg_desc,b.lpcnt,b.lavgfee,b.lavgday from( \n ");
			sql.append("  select b.pardrg_code,drg.drg_desc,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("  left join drgs.t_drgs drg  \n ");
			sql.append("  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append( monitorTypeCondition );
			sql.append("  group by b.pardrg_code,drg.drg_desc \n ");
			sql.append("  order by lpcnt desc ) b)b \n ");
			sql.append("  on a.pardrg_code = b.pardrg_code \n ");
			sql.append("  left join DRGs.t_annualstd c \n ");
			sql.append("  on c.as_drg_code = b.pardrg_code and  c.as_id = '"+stdId+"'  \n ");
			sql.append("  order by tpcnt desc) t \n ");
			break;
		case "thisNo":
			sql.setLength(0);
			sql.append("  select rownum lastorder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("   select b.pardrg_code drgCode,b.drg_desc drgDesc,0 tpcnt,0 avgfee,0 avgday,b.lpcnt lpcnt,b.lavgfee lavgfee,b.lavgday lavgday,c.as_weight weight from   \n ");
			sql.append("   (select b.pardrg_code,b.drg_desc,b.lpcnt,b.lavgfee,b.lavgday from(  \n ");
			sql.append("   select b.pardrg_code,drg.drg_desc,count(*) lpcnt,avg(a.P00800000) lavgfee,avg(a.P00320000) lavgday  \n ");
			sql.append("   from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join drgs.t_drgs drg  \n ");
			sql.append("   on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on a.P00300001=dep.dep_code   \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'  \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append( monitorTypeCondition );
			sql.append("   and b.pardrg_code not in ( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a    \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b      \n ");
			sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("   left join DRGs.t_department dep    \n ");
			sql.append("   on a.P00300001=dep.dep_code    \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   ) \n ");
			sql.append("   group by b.pardrg_code,drg.drg_desc  \n ");
			sql.append("   order by lpcnt desc ) b)b  \n ");
			sql.append("   left join DRGs.t_annualstd c  \n ");
			sql.append("   on c.as_drg_code = b.pardrg_code and c.as_id = '"+stdId+"'  \n ");
			sql.append("   order by lpcnt desc) t \n ");
			break;
		case "lastNo":
			sql.setLength(0);
			sql.append("  select rownum thisOrder,");
			if(overage){
				sql.append("  case when d.pardrg_code is not null then 1 else 0 end cover,");				
			}
			sql.append("  t.* from(");
			sql.append("   select  a.pardrg_code drgCode ,a.drg_desc drgDesc,a.tpcnt,a.avgfee avgfee,a.avgday avgday,0 lpcnt,0 lavgfee,0 lavgday,c.as_weight weight from   \n ");
			sql.append("   (select a.pardrg_code,a.drg_desc,a.tpcnt,a.avgfee,a.avgday from(  \n ");
			sql.append("   select b.pardrg_code,drg.drg_desc,count(*) tpcnt,avg(a.P00800000) avgfee,avg(a.P00320000) avgday  \n ");
			sql.append("   from DRGs.mr_ipinfo a   \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b    \n ");
			sql.append("   on a.pmainindex=b.parid   and  b.ver_region='"+groupScheme+"'  \n ");
			sql.append("   left join drgs.t_drgs drg   \n ");
			sql.append("   on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE   \n ");
			sql.append("   left join DRGs.t_department dep   \n ");
			sql.append("   on a.P00300001=dep.dep_code   \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"'  \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null  \n ");
			sql.append( monitorTypeCondition );
			sql.append("   and  b.pardrg_code not in( \n ");
			sql.append("   select distinct(b.pardrg_code) from DRGs.mr_ipinfo a     \n ");
			sql.append("   left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("   left join DRGs.t_patientdrg b     \n ");
			sql.append("   on a.pmainindex=b.parid  and  b.ver_region='"+groupScheme+"'     \n ");
			sql.append("   left join DRGs.t_department dep     \n ");
			sql.append("   on a.P00300001=dep.dep_code     \n ");
			sql.append("   where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"'   \n ");
			sql.append("   and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("   and b.pardrg_code is not null   \n ");
			sql.append("   ) \n ");
			sql.append("   group by b.pardrg_code,drg.drg_desc   \n ");
			sql.append("   order by tpcnt desc ) a) a   \n ");
			sql.append("   left join DRGs.t_annualstd c  \n ");
			sql.append("   on c.as_drg_code = a.pardrg_code and c.as_id = '"+stdId+"' \n ");
			sql.append("   order by tpcnt desc) t  \n ");
			break;
		}
		//追加查询条件
		if(overage){
			sql.append( conditionOver );				
		}
		List<Map<String,Object>> result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	}

	/**
	 * @Description:理论内未覆盖DRGs详情
	 * @author shaozhiyuan
	 * @date 2018年9月6日
	 * @version V3.1.1
	 */
	@Override
	@Cacheable(value = "drgsCaseDetail3.1.1", key = "'getNotInDrgData' + #drgsAnalysis.depCode + #drgsAnalysis.depGroupCode + #drgsAnalysis.hospCode"
			+" + #drgsAnalysis.stdId + #drgsAnalysis.verRegion + #drgsAnalysis.dateFrom + #drgsAnalysis.dateTo + #drgsAnalysis.queryType ")
	public List<Map<String, Object>> getNotInDrgData(DrgsAnalysis drgsAnalysis) {
		String depCode = drgsAnalysis.getDepCode();
		String depGroupCode = drgsAnalysis.getDepGroupCode();
		String hospCode = drgsAnalysis.getHospCode();
    	String dateFrom= drgsAnalysis.getDateFrom();
		String dateTo=drgsAnalysis.getDateTo();
		String queryType = drgsAnalysis.getQueryType();
		String stdId = drgsAnalysis.getStdId();//标杆的id
		String groupScheme = drgsAnalysis.getVerRegion();
		int thisYear = Integer.parseInt(dateFrom.substring(0,4));//当年
    	String monthFrom=dateFrom.substring(5);	//起始月份
		String monthTo=dateTo.substring(5);	//起始月份
    	String condition = "";//条件字符串
    	//monitorType是 类型比较  all:DRGs组数   in:理论内DRG组数 out:理论外DRG组数 notin:理论内未覆盖DRGs组数
    	//String monitorType=drgsAnalysis.getMonitorType(); 
		//科室组
		if (StringUtils.hasLength(depGroupCode)&&!"null".equals(depGroupCode)&&!"hosp".equals(depGroupCode)) {
			condition = "and dep.pardepgroup_code = '"+depGroupCode+"'";
		} 
		//科室
		if (StringUtils.hasLength(depCode)&&!"null".equals(depCode)) {
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				condition = "and dep.dep_code in ("+depCode+")";
			}
			else
			{//一个科室
				condition = "and dep.dep_code = '"+depCode+"'";
			}
		}
		
		String  headSql=""; //sql头部查询条件
		headSql+=" \n ";
		headSql+=" select depdrg.PARDRG_CODE DECC ,drg.drg_desc NAME,std.AS_WEIGHT WEIGHT  \n ";
		headSql+=" ,std.AS_AVG_FEE FEE ,std.AS_AVG_DAYS DAYS \n ";
		headSql+=" ,case std.AS_RISK_LEVEL when 0 then '无' when 1 then '低风险组' when 2 then '中低风险组' when 3 then '中高风险组' when 4 then '高风险组'  else '' end RISKLEVEL \n ";
		headSql+=" from DRGs.t_departmentdrg depdrg left join DRGs.t_department dep on  dep.dep_code=depdrg.pardep_code \n ";
		headSql+=" left join DRGs.t_drgs drg on drg.DRG_CODE=depdrg.PARDRG_CODE and drg.VER_CODE = depdrg.version \n ";
		headSql+=" left join DRGs.T_ANNUALSTD std on std.AS_DRG_CODE=depdrg.PARDRG_CODE and std.as_id='"+stdId+"' \n ";
		
		StringBuilder sql  = new StringBuilder();
		switch(queryType)
		{
		case "this":
			sql.setLength(0);
			sql.append(headSql);
			sql.append(" where 1=1 \n ");
			sql.append( condition ); //科室条件
			sql.append(" and  depdrg.version = ( \n ");
			sql.append(" SELECT a.ver_code  FROM ( \n ");
			sql.append(" select pd.ver_code,count(1) cnt from drgs.t_patientdrg pd  \n ");
			sql.append(" where pd.ver_code IS NOT NULL and pd.ver_region='"+groupScheme+"' \n ");
			sql.append(" group BY pd.ver_code ) a  WHERE Rownum=1 ) \n ");
			sql.append(" and depdrg.pardrg_code not in (  \n ");
			sql.append("  select distinct b.pardrg_code \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
			sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null ) \n ");
			break;
		case "last":
			sql.setLength(0);
			sql.append(headSql);
			sql.append(" where 1=1 \n ");
			sql.append( condition ); //科室条件
			sql.append(" and  depdrg.version = ( \n ");
			sql.append(" SELECT a.ver_code  FROM ( \n ");
			sql.append(" select pd.ver_code,count(1) cnt from drgs.t_patientdrg pd  \n ");
			sql.append(" where pd.ver_code IS NOT NULL and pd.ver_region='"+groupScheme+"' \n ");
			sql.append(" group BY pd.ver_code ) a  WHERE Rownum=1 ) \n ");
			sql.append(" and depdrg.pardrg_code not in (  \n ");
			sql.append("  select distinct b.pardrg_code \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
			sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null ) \n ");
			break;
		case "thisNo":
			sql.setLength(0);
			sql.append(headSql);
			sql.append(" where 1=1 \n ");
			sql.append( condition ); //科室条件
			sql.append(" and  depdrg.version = ( \n ");
			sql.append(" SELECT a.ver_code  FROM ( \n ");
			sql.append(" select pd.ver_code,count(1) cnt from drgs.t_patientdrg pd  \n ");
			sql.append(" where pd.ver_code IS NOT NULL and pd.ver_region='"+groupScheme+"' \n ");
			sql.append(" group BY pd.ver_code ) a  WHERE Rownum=1 ) \n ");
			sql.append(" and depdrg.pardrg_code not in (  \n ");
			sql.append("  select distinct b.pardrg_code \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
			sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null and b.pardrg_code not in (  \n ");
			sql.append("  select distinct b.pardrg_code \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
			sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  ) ) \n ");
			break;
		case "lastNo":
			sql.setLength(0);
			sql.append(headSql);
			sql.append(" where 1=1 \n ");
			sql.append( condition ); //科室条件
			sql.append(" and  depdrg.version = ( \n ");
			sql.append(" SELECT a.ver_code  FROM ( \n ");
			sql.append(" select pd.ver_code,count(1) cnt from drgs.t_patientdrg pd  \n ");
			sql.append(" where pd.ver_code IS NOT NULL and pd.ver_region='"+groupScheme+"' \n ");
			sql.append(" group BY pd.ver_code ) a  WHERE Rownum=1 ) \n ");
			sql.append(" and depdrg.pardrg_code not in (  \n ");
			sql.append("  select distinct b.pardrg_code \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+(thisYear-1)+"-"+monthFrom+"' and '"+(thisYear-1)+"-"+monthTo+"' \n ");
			sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null and b.pardrg_code not in (  \n ");
			sql.append("  select distinct b.pardrg_code \n ");
			sql.append("  from DRGs.mr_ipinfo a  \n ");
			sql.append("  left join DRGs.mr_baseinfo bs on a.pmainindex=bs.pmainindex    \n ");
			sql.append("  left join DRGs.t_patientdrg b   \n ");
			sql.append("  on a.pmainindex=b.parid and  b.ver_region='"+groupScheme+"' \n ");
			sql.append("  left join DRGs.t_department dep  \n ");
			sql.append("  on a.P00300001=dep.dep_code  \n ");
			sql.append("  where to_char(a.P00290000,'yyyy-mm') between '"+dateFrom+"' and '"+dateTo+"' \n ");
			sql.append("  and instr('"+hospCode+"',bs.p00010001)>0 \n ");
			sql.append( condition );
			sql.append("  and b.pardrg_code is not null \n ");
			sql.append("  ) ) \n ");
			break;
		}
		
		List<Map<String,Object>> result = jdbcTemplateObj.queryForList(sql.toString()) ;
		return result;
	}

}
